from utils import log_info, log_exception, log_error, log_debug
from .db_pool import get_db_pool

class DatabaseInitializer:
    """数据库初始化类，统一管理所有表结构的创建和删除"""

    @staticmethod
    def init_all_tables():
        """初始化所有必要的表结构"""
        conn = None
        try:
            # 从连接池获取连接
            db_pool = get_db_pool()
            conn = db_pool.get_connection()
            if not conn:
                log_error("无法从连接池获取数据库连接")
                return False
            cursor = conn.cursor()
            log_debug(f"从连接池获取连接成功，进行表结构初始化")

            # 创建用户表
            DatabaseInitializer._create_users_table(cursor)
            
            # 创建错误题目表
            DatabaseInitializer._create_error_questions_table(cursor)
            
            # 创建收藏题目表
            DatabaseInitializer._create_favorite_questions_table(cursor)
            
            # 创建练习进度表
            DatabaseInitializer._create_practice_progress_table(cursor)
            
            # 创建考试记录表
            DatabaseInitializer._create_exam_records_table(cursor)
            
            # 创建考试题目详情表
            DatabaseInitializer._create_exam_question_details_table(cursor)
            
            # 创建题库表
            DatabaseInitializer._create_questions_table(cursor)
            
            # 删除不再使用的表
            DatabaseInitializer._drop_old_tables(cursor)
            
            conn.commit()
            return True
        except Exception as e:
            log_exception(f"初始化表结构时发生异常: {str(e)}")
            return False
        finally:
            # 确保连接被释放
            if conn:
                db_pool.release_connection(conn)
                log_debug("所有表结构初始化完成，连接已释放回连接池")


    @staticmethod
    def _create_users_table(cursor):
        """创建用户表"""
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE,
            id_card TEXT UNIQUE,
            last_login TIMESTAMP
        )
        ''')
        log_debug("已创建用户表")

    @staticmethod
    def _create_error_questions_table(cursor):
        """创建错误题目表"""
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS error_questions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question_id INTEGER,
            category TEXT,
            user_id INTEGER,
            FOREIGN KEY (question_id) REFERENCES questions (id),
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')
        log_debug("已创建错误题目表")

    @staticmethod
    def _create_favorite_questions_table(cursor):
        """创建收藏题目表"""
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS favorite_questions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question_id INTEGER,
            category TEXT,
            user_id INTEGER,
            FOREIGN KEY (question_id) REFERENCES questions (id),
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')
        log_debug("已创建收藏题目表")

    @staticmethod
    def _create_practice_progress_table(cursor):
        """创建练习进度表"""
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS practice_progress (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category TEXT,
            current_index INTEGER,
            last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            user_id INTEGER,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')
        log_debug("已创建练习进度表")

    @staticmethod
    def _create_exam_records_table(cursor):
        """创建考试记录表"""
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS exam_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category TEXT,
            exam_date TIMESTAMP,
            user_id INTEGER,
            duration_seconds REAL,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')
        log_debug("已创建考试记录表")

    @staticmethod
    def _create_exam_question_details_table(cursor):
        """创建考试题目详情表"""
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS exam_question_details (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            exam_id INTEGER,
            question_id INTEGER,
            question_text TEXT,
            option_a TEXT,
            option_b TEXT,
            option_c TEXT,
            option_d TEXT,
            correct_answer TEXT,
            user_answer TEXT,
            is_correct BOOLEAN,
            type INTEGER,
            image_data TEXT,
            FOREIGN KEY (exam_id) REFERENCES exam_records (id),
            FOREIGN KEY (question_id) REFERENCES questions (id)
        )
        ''')
        log_debug("已创建考试题目详情表")

    @staticmethod
    def _create_questions_table(cursor):
        """创建题库表"""
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS questions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            J TEXT,
            P TEXT,
            I TEXT,
            Q TEXT,
            T TEXT,
            A TEXT,
            B TEXT,
            C TEXT,
            D TEXT,
            F TEXT,
            LA TEXT,
            LB TEXT,
            LC TEXT,
            type INTEGER
        )
        ''')
        log_debug("已创建题库表")

    @staticmethod
    def _drop_old_tables(cursor):
        """删除不再使用的表"""
        # 删除recent_exams表
        cursor.execute("DROP TABLE IF EXISTS recent_exams")
        log_debug("已删除旧表: recent_exams")

    @staticmethod
    def reset_questions_table():
        """重置题库表（删除并重新创建）"""
        try:
            # 从连接池获取连接
            db_pool = get_db_pool()
            conn = db_pool.get_connection()
            if not conn:
                log_error("无法从连接池获取数据库连接")
                return False
            cursor = conn.cursor()
            
            # 删除原题库表
            cursor.execute("DROP TABLE IF EXISTS questions")
            log_debug("已删除原题库表")
            
            # 重新创建题库表
            DatabaseInitializer._create_questions_table(cursor)
            conn.commit()
            return True
        except Exception as e:
            log_exception(f"重置题库表时发生异常: {str(e)}")
            return False
        finally:
            # 确保连接被释放
            if conn:
                db_pool.release_connection(conn)
                log_debug("重置题库表完成，连接已释放回连接池")

    @staticmethod
    def clear_user_data(user_id):
        """清空指定用户的数据"""
        log_debug(f"开始清空用户 {user_id} 的数据")
        db_pool = get_db_pool()
        conn = db_pool.get_connection()
        cursor = conn.cursor()
        
        # 清空该用户的错题本
        cursor.execute('DELETE FROM error_questions WHERE user_id = ?', (user_id,))
        
        # 清空该用户的练习进度
        cursor.execute('DELETE FROM practice_progress WHERE user_id = ?', (user_id,))
        
        # 清空该用户的考试记录
        cursor.execute('DELETE FROM exam_records WHERE user_id = ?', (user_id,))
        
        # 清空该用户的考试题目详情
        cursor.execute('DELETE FROM exam_question_details WHERE exam_id IN (SELECT id FROM exam_records WHERE user_id = ?)', (user_id,))
        
        # 清空该用户的收藏题目
        cursor.execute('DELETE FROM favorite_questions WHERE user_id = ?', (user_id,))
        
        conn.commit()
        conn.close()
        log_debug(f"成功清空用户 {user_id} 的数据")

def init_database():
    """初始化数据库"""
    return DatabaseInitializer.init_all_tables()

def reset_questions():
    """重置题库表"""
    return DatabaseInitializer.reset_questions_table()

def clear_user_data(user_id):
    """清空指定用户的数据"""
    return DatabaseInitializer.clear_user_data(user_id)
